<html>
<head>
<title>Tutorial 1 Stage 3 - Working with expressions using assignments</title>
<link rel="stylesheet" type="text/css" href="../media/style.css">
</head>
<body>

<table border="0" cellspacing="0" cellpadding="0" height="48" width="100%">
  <tr>
    <td width="100%">
			<table width="100%" height="78" border="0" align="left" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" id="topnav">
			<tr>
				<!-- Header Side -->
				<td width="17">
					<img height="78" src="media/reportico100.png"/>
				</td>
				<td align="right"></td>
				<td align="center">
					<table width="100%" height="60" border="0" align="left" cellpadding="0" cellspacing="0">
						<tr>
							<td valign="top" align="left">
							<img src="media/repbanner.png"/></td>
							</td>
						</tr>
						<tr>
							<td align="center" valign="bottom" class="topmenucell">
								<nobr>
								&nbsp;
								</nobr>
							</td>
						</tr>
					</table>
				</td>
				<td width="103" class="toplogincell">
					&nbsp;
				</td>
			</tr>
			</table>
</td>
  </tr>
  <!--tr>
    <td class="header_top">reportico</td>
  </tr-->
  <tr><td class="header_line"><img src="../media/empty.png" width="1" height="1" border="0" alt=""  /></td></tr>
  <tr>
    <td class="header_menu">
  		  [ <a href="../classtrees_reportico.html" class="menu">class tree: reportico</a> ]
		  [ <a href="../elementindex_reportico.html" class="menu">index: reportico</a> ]
		  [ <a href="../elementindex.html" class="menu">all elements</a> ]
    </td>
  </tr>
  <tr><td class="header_line"><img src="../media/empty.png" width="1" height="1" border="0" alt=""  /></td></tr>
</table>

<table width="100%" border="0" cellpadding="0" cellspacing="0">
  <tr valign="top">
    <td width="200" class="menu">
      <b>Packages:</b><br />
              <a href="../li_reportico.html">reportico</a><br />
            <br /><br />
		<b>Tutorials/Manuals:</b><br />
					<strong>Package-level:</strong>
							<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.pkg.html">Reportico Tutorial And User Manual</a>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.manual.pkg.html">Reportico User Manual</a>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.quickstart.pkg.html">Reportico Installation</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.gettingstarted.pkg.html">Getting Started</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.structure.pkg.html">Reportico Installation Structure</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.using.pkg.html">Configuring Reportico - System Parameters</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.embedding.pkg.html">Embedding Reportico</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.security.pkg.html">Reportico Security</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.language.pkg.html">Language Support</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.projects.pkg.html">Projects</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.importlink.pkg.html">Importing and linking from reports</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.menus.pkg.html">Report Menus</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.prepare.pkg.html">Report Preparation and Execution</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.design.pkg.html">Report Design Mode</a>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.format.pkg.html">The Design Format Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.customcode.pkg.html">Custom Source Code</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.qrydet.pkg.html">The Query Details Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.assign.pkg.html">The Assignments Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.criteria.pkg.html">The Criteria Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.output.pkg.html">The Output Menu</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.webservices.pkg.html">Turning Reports into Web Services (currently unavailable)</a>
</ul>
</li></ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.faq.pkg.html">Reportico FAQ</a>
</ul>
</li></ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.howto.pkg.html">Reportico Tutorial</a>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1.pkg.html">Tutorial No.1 - The Film Listing Report</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_1.pkg.html">Tutorial 1 Stage 1 - Creating a basic report</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_2.pkg.html">Tutorial 1 Stage 2 - Creating User Entry Criteria</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_3.pkg.html">Tutorial 1 Stage 3 - Working with expressions using assignments</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_4.pkg.html">Tutorial 1 Stage 4 - Working with groups</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial1_5.pkg.html">Tutorial 1 Stage 5 - Including database graphics into the report and generating drilldowns</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial2_1.pkg.html">Tutorial 2 - Creating the loan history report</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial3_1.pkg.html">Tutorial 3 - The Monthly Returns Report</a>
</ul>
<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.tutorial4_1.pkg.html">Tutorial 4 - The Late Returns Summary report</a>
</ul>
</li></ul>
</li></ul>

							<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.appendix1.pkg.html">Appendix 1 - Reportico Project Configuration parameters</a>
</ul>

							<ul>
	<li type="square"><a href="../reportico/tutorial_reportico.appendix2.pkg.html">Appendix 2 - Reportico URL request parameters</a>
</ul>

									                        <b>Files:</b><br />
      	  <div class="package">
			<a href="../reportico/_reportico.php.html">		reportico.php
		</a><br>
	  </div><br />
      
                </td>
    <td>
      <table cellpadding="10" cellspacing="0" width="100%" border="0"><tr><td valign="top">

<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="10%" align="left" valign="bottom"><a href=
"../reportico/tutorial_reportico.tutorial1_2.pkg.html">Prev</a></td>
<td width="80%" align="center" valign="bottom"></td>
<td width="10%" align="right" valign="bottom"><a href=
"../reportico/tutorial_reportico.tutorial1_4.pkg.html">Next</a></td>
</tr>
</table>
<span><a name="language."></a><h2 class="title">Tutorial 1 Stage 3 - Working with expressions using assignments</h2><p><strong class = "bold">Note that this tutorial requires you to enter assignments using PHP code. Assignment code may not be
      entered when running in SAFE mode as the user could create SQL
      statements here that could delete and update data in the
      database or perform disk operations. By default, the tutorial projects have SAFE mode turned on. To continue with this tutorial you need to turn off SAFE mode by folowing the instructions on how to turn on and off Safe Mode in the <a href="../reportico/tutorial_reportico.security.pkg.html">Reportico Security</a> section.</strong></p>
    <p>In this stage you will learn how to :-
	<ol><li><p>Create a string expression column</p></li>
	<li><p>Create a cumulative expression</p></li>
	<li><p>Create a line count expression</p></li>
	<li><p>Use an assignment within a criteria item</p></li></ol></p>
   <a name="tut1_3_run"></a><h3 class="title">Beginning the Tutorial 1 Stage 3 Report</h3><p>Select <strong>Film Listing - Tutorial 1 Stage 3</strong> from the tutorials menu (Alternatively you can point your browser at the URL <strong>http://{SERVER_ADDRESS}/{REPORTICO_INSTALL_DIRECTORY}/run.php?project=tutorials&amp;execute_mode=PREPARE&amp;xmlin=tut1_3_films.xml</strong>). You are now ready to start the third stage of this tutorial.</p>
<p>Note that the starting point for this stage follows directly from stage 2 and is almost the same report that you created in stage 2 except we now have the year showing in the main report.</p><hr />
   <a name="tut1_3_simpexp"></a><h3 class="title">Create a string expression column</h3><p>In this stage you will make the Film Id appear on the report as a 4-digit number padded to the left with zeroes.</p>
   <p>Enter the report design mode and select the <strong>Assignments</strong> tab. Click <strong>Add</strong> to enter a new assignment and click on the button created marked <strong>Assignment Column</strong>. You should see a set of entry fields named <strong>Assign To</strong>,<strong>Expression</strong> etc. Here you select the column you wish to make the assignment to and expression in the form of a PHP expression.</p>
   <p>Enter the following parameters:-
<table border = "1"><colgroup span = "1"/><thead><td></td>
  <td>Parameter</td>
  <td>Value</td>
  <td>Description</td></thead>
<tbody><tr><td>1.</td>
  <td>Assign To</td>
  <td>film_id</td>
  <td>An internal name to give to the criteria item. You would use this name to refer to this criteria elsewhere in the report tool</td></tr>
<tr><td>2.</td>
  <td>Expression</td>
  <td><pre class="listing"><strong>str_pad</strong>({film_id},4,&quot;0&quot;,STR_PAD_LEFT)</pre></td>
  <td>Call the PHP str_pad function to format the column with zeroes. Note the use of <strong>{film_id}</strong> entry to indicate a report column that will be reformatted within this functon. At run time the value of the film_id column for each row will be passed to this function and then assigned back to itself.</td></tr>
<tr><td>3.</td>
  <td>Condition</td>
  <td>&nbsp;</td>
  <td>Leave blank.</td></tr></tbody></table></p>
<p>Run the report and check that the film Id column is zero padded.</p><hr />
   <a name="tut1_3_cumulexp"></a><h3 class="title">Create a cumulative expression</h3><p>In this stage you will create 4 new columns to appear on the report that represent a rolling minimum, maximum, sum and average.</p>
   <p>This time you need to manually add 4 new assignments to columns that don't yet exist. So enter the report design mode and select the <strong>Assignments</strong> tab. Now click on the  <strong>Add</strong> button, then click on the resulting  <strong>Assignment Column</strong> button and set the  <strong>Assign to New Column</strong> parameter value to  be <strong>minyear</strong>. Set this to <strong>min({year})</strong> and press <strong>Ok</strong>. Note that you can also use the <strong>Aggregates Wizard</strong> to implement the same minimum function. Now do this another 3 times adding columns named  <strong>maxyear</strong>,  <strong>sumyear</strong> and  <strong>avgyear</strong> and setting the these to the values below..</p>
   <p><table border = "1"><colgroup span = "1"/><thead><td></td>
  <td>Assign To</td>
  <td>Expression</td>
  <td>Description</td></thead>
<tbody><tr><td>1.</td>
  <td>minyear</td>
  <td><pre class="listing">min({year})</pre></td>
  <td>When the report runs the minyear column will show the minimum year so far reported</td></tr>
<tr><td>2.</td>
  <td>maxyear</td>
  <td><pre class="listing">max({year})</pre></td>
  <td>When the report runs the maxyear column will show the maximum year so far reported line</td></tr>
<tr><td>3.</td>
  <td>sumyear</td>
  <td><pre class="listing">sum({year})</pre></td>
  <td>When the report runs the sumyear column will show cumulative year sum</td></tr>
<tr><td>4.</td>
  <td>avgyear</td>
  <td><pre class="listing">avg({year})</pre></td>
  <td>When the report runs the avgyear column will show cumulative year average</td></tr></tbody></table></p>
<p>Run the report and check that your rolling/cumulative values are set.</p><hr />
   <a name="tut1_3_lineexp"></a><h3 class="title">Create a line count expression</h3><p>In this stage you will create a new report column showing the line number</p>
   <p>Select the <strong>Assignments</strong> tab. Click <strong>Add</strong> to enter a new assignment. Set the following parameters.</p>
   <p>Enter the following parameters:-
<table border = "1"><colgroup span = "1"/><thead><td></td>
  <td>Parameter</td>
  <td>Value</td>
  <td>Description</td></thead>
<tbody><tr><td>1.</td>
  <td>Assign To New Column</td>
  <td>count</td>
  <td>To set the count column</td></tr>
<tr><td>2.</td>
  <td>Expression</td>
  <td><pre class="listing">lineno()</pre></td>
  <td>Function to set the line count</td></tr>
<tr><td>3.</td>
  <td>Condition</td>
  <td>&nbsp;</td>
  <td>Leave blank.</td></tr></tbody></table></p><hr />
   <a name="tut1_3_critass"></a><h3 class="title">Use an assignment within a criteria item</h3><p>In this stage you will use an expression within the film criteria item.</p>
   <p>Enter report design mode and select the <strong>Criteria</strong> tab, then select the <strong>Criteria film</strong> button.</p>
   <p>Select the <strong>criteria=&gt;Assignments</strong> sub tab ( not the top <strong>Assignments</strong> which relates to the main query. We will assign a new criteria column with a string containing a formatted combination of the film id and the film title. Add a new assignment and set the following parameters :-</p>
   <p>Enter the following parameters:-
<table border = "1"><colgroup span = "1"/><thead><td></td>
  <td>Parameter</td>
  <td>Value</td>
  <td>Description</td></thead>
<tbody><tr><td>1.</td>
  <td>Assign To New Column</td>
  <td>full</td>
  <td>To set the full description</td></tr>
<tr><td>2.</td>
  <td>Expression</td>
  <td><pre class="listing">str_pad({film_id},4,&quot;0&quot;,STR_PAD_LEFT).&quot;.&amp;nbsp;&amp;nbsp;&quot;.{title}</pre></td>
  <td>Sets the full column to be a zero padded film id concatenated with the film title</td></tr>
<tr><td>3.</td>
  <td>Condition</td>
  <td>&nbsp;</td>
  <td>Leave blank.</td></tr></tbody></table></p>
<p>To make use of the value within the criteria, you will need to select the <strong>Details</strong> subtab and set the <strong>Display Column</strong> to be <strong>full</strong>.</p>
<p>Run the report and click the film criteria expand(<strong>&gt;&gt;</strong>) button. You should see the film list comprises both film id and title.</p><hr />
   <a name="tut1_2_check"></a><h3 class="title">Checking Your Report</h3><p>After you have successfully completed the above steps, check the whole report :-</p>
<p>Execute the report to get a list of films. On the report output you will see the first column of Film Id with values left-padded with zeroes. This is the result of the assignment that reformats the Film Id value.</p>
<p>Also notice the minyear and maxyear, avgyear and sumyear columns. They represent the cumulative minimum, maximum, average and sum of the year column. Obviously the sum and average of the year is pretty meaningless but it does demonstrate these functions.</p>
<p>Notice the Line column is an expression assignment which is the report line number</p>
<p>Finally, return to the report entry screen and expand the film criteria. You will see that the displayed film list contains values that comprise the film id concatenated with the film title. This is performed with an expression assignment within the criteria.</p><hr /></span>
<table width="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td width="33%" align="left" valign="top"><a href="../reportico/tutorial_reportico.tutorial1_2.pkg.html">Prev</a></td>
<td width="34%" align="center" valign="top"><a href=
"../reportico/tutorial_reportico.howto.pkg.html">Up</a></td>
<td width="33%" align="right" valign="top"><a href=
"../reportico/tutorial_reportico.tutorial1_4.pkg.html">Next</a></td>
</tr>

<tr>
<td width="33%" align="left" valign="top">Tutorial 1 Stage 2 - Creating User Entry Criteria</td>
<td width="34%" align="center" valign="top">Reportico Tutorial</td>
<td width="33%" align="right" valign="top">Tutorial 1 Stage 4 - Working with groups</td>
</tr>
</table>
        <div class="credit">
		    <hr />
		    Documentation generated on Mon, 09 Jun 2014 18:10:08 +0100 by <a href="http://www.phpdoc.org">phpDocumentor 1.4.3</a>
	      </div>
      </td></tr></table>
    </td>
  </tr>
</table>

</body>
</html>